package com.qjy.sqldemo;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.qjy.bean.AddressBean;
import com.qjy.bean.BookmarkBean;
import com.qjy.bean.HistoryBean;
import com.qjy.bean.HomeBookBean;


import java.util.ArrayList;
import java.util.List;

public class SQLiteUtils {
    private SQLiteDbHelper sqLiteDbHelper;
    private SQLiteDatabase db;

    public SQLiteUtils(Context mContext) {
        sqLiteDbHelper = new SQLiteDbHelper(mContext);
        db = sqLiteDbHelper.getWritableDatabase();
    }


    //----------------------------- 地址栏数据库操作 ---------------------------------
    /**
     * 地址栏数据插入
     * @param addressBean
     *
     */
    public void insertAddress(AddressBean addressBean){
        String sql = "INSERT INTO "+ SQLiteDbHelper.BROWSER_ADDRESS +" (addressId ,addressUrl) VALUES (NULL,?)";
        Object[] args = {addressBean.getAddressUrl()};
        db.execSQL(sql, args);
    }

    /**
     * 地址栏查询全部数据
     */
    public List<AddressBean> queryAllAddress(){
        //查询全部数据
        Cursor cursor = db.query(SQLiteDbHelper.BROWSER_ADDRESS,null, null, null, null, null, null);
        List<AddressBean> list = new ArrayList<>();
        if(cursor.getCount() > 0){
            //移动到首位
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                int addressId = cursor.getInt(cursor.getColumnIndex("addressId"));
                String addressUrl = cursor.getString(cursor.getColumnIndex("addressUrl"));
                AddressBean addressBean = new AddressBean();
                addressBean.setAddressId(addressId);
                addressBean.setAddressUrl(addressUrl);
                list.add(addressBean);
                cursor.moveToNext();
            }
        }
        cursor.close();
        return list;
    }

    /**
     * 查询地址栏数据是否存在
     * @param url
     *
     */
    public boolean selectAddress(String url){
        String sql = "SELECT * FROM "+ SQLiteDbHelper.BROWSER_ADDRESS +" WHERE addressUrl = ?";
        String[] args = {url};
        Cursor cursor = db.rawQuery(sql, args);
        while (cursor.moveToNext()) {
//            deleAddress(cursor.getInt(cursor.getColumnIndex("addressId")));//存在则删除
            cursor.close();
            return true; //在数据库已存在，返回true
        }
        cursor.close();
        return false;
    }

    /**
     * 删除单个地址栏
     * @param id
     */
    public void deleAddress(int id) {
        String sql = "DELETE FROM "+ SQLiteDbHelper.BROWSER_ADDRESS +" WHERE "+" addressId ="+"?";
        Object bindArgs[] = new Object[] {id};
        db.execSQL(sql, bindArgs);
    }
    /*
     * 删除地址栏全部数据
     */
    public void deleAllAddress() {
        String sql = "DELETE FROM "+ SQLiteDbHelper.BROWSER_ADDRESS ;
        db.execSQL(sql);
    }


    //-----------------------------书签数据库操作---------------------------------
    /**
     * 书签数据插入
     * @param bookmarkBean
     *
     */

    public void insertBookMark(BookmarkBean bookmarkBean){
        String sql = "INSERT INTO " + SQLiteDbHelper.BROWSER_BOOKMARK + "(bookmarkId,bookmarkTitle,bookmarkUrl,bookmarkTime)values(null,?,?,?,?)";
        Object[] args = {bookmarkBean.getBookmarkTitle(),bookmarkBean.getBookmarkUrl(),bookmarkBean.getBookmarkTime()};
        db.execSQL(sql, args);
//        db.close();
    }
    /**
     * 书签数据查询全部数据
     */
    public List<BookmarkBean> queryAllBookMark(){
        //查询全部数据
        Cursor cursor = db.query(SQLiteDbHelper.BROWSER_BOOKMARK,null, null, null, null, null, null);
        List<BookmarkBean> list = new ArrayList<>();
        if(cursor.getCount() > 0){
            //移动到首位
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                int addressId = cursor.getInt(cursor.getColumnIndex("bookmarkId"));
                String bookmarkTitle = cursor.getString(cursor.getColumnIndex("bookmarkTitle"));
                String bookmarkUrl = cursor.getString(cursor.getColumnIndex("bookmarkUrl"));
                String bookmarkTime = cursor.getString(cursor.getColumnIndex("bookmarkTime"));
                BookmarkBean bookmarkBean = new BookmarkBean();
                bookmarkBean.setBookmarkId(addressId);
                bookmarkBean.setBookmarkTitle(bookmarkTitle);
                bookmarkBean.setBookmarkUrl(bookmarkUrl);
                bookmarkBean.setBookmarkTime(bookmarkTime);
                list.add(bookmarkBean);
                cursor.moveToNext();
            }
        }
        cursor.close();
        //db.close();
        return list;
    }

    /**
     * 删除单个书签
     * @param id
     */
    public void deleBookmark(int id) {
        String sql = "DELETE FROM "+ SQLiteDbHelper.BROWSER_BOOKMARK +" WHERE "+" bookmarkId ="+"?";
        Object bindArgs[] = new Object[] {id};
        db.execSQL(sql, bindArgs);
    }
    /*
     * 删除书签全部数据
     */
    public void deleAllBookmark() {
        String sql = "DELETE FROM "+ SQLiteDbHelper.BROWSER_BOOKMARK ;
        db.execSQL(sql);
    }

    //-----------------------------历史数据库操作---------------------------------
    /**
     * 历史数据插入
     * @param historyBean
     *
     */
    public void insertHistory(HistoryBean historyBean){
        String sql = "INSERT INTO " + SQLiteDbHelper.BROWSER_HISTORY + "(historyId,historyTitle,historyUrl)values(null,?,?,?)";
        Object[] args = {historyBean.getHistoryTitle(),historyBean.getHistoryUrl()};
        db.execSQL(sql, args);
//        db.close();
    }

    /**
     * 历史数据查询全部数据
     */
    public List<HistoryBean> queryAllHistory(){
        //查询全部数据
        Cursor cursor = db.query(SQLiteDbHelper.BROWSER_HISTORY,null, null, null, null, null, null);
        List<HistoryBean> list = new ArrayList<>();
        if(cursor.getCount() > 0){
            //移动到首位
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                int historyId = cursor.getInt(cursor.getColumnIndex("historyId"));
                String historyTitle = cursor.getString(cursor.getColumnIndex("historyTitle"));
                String historyUrl = cursor.getString(cursor.getColumnIndex("historyUrl"));
                HistoryBean historyBean = new HistoryBean();
                historyBean.setHistoryId(historyId);
                historyBean.setHistoryTitle(historyTitle);
                historyBean.setHistoryUrl(historyUrl);
                list.add(historyBean);
                cursor.moveToNext();
            }
        }
        cursor.close();
        //db.close();
        return list;
    }
    /**
     * 删除单个历史
     * @param id
     */
    public void deleHistory(int id) {
        String sql = "DELETE FROM "+ SQLiteDbHelper.BROWSER_HISTORY +" WHERE "+" historyId ="+"?";
        Object bindArgs[] = new Object[] {id};
        db.execSQL(sql, bindArgs);
    }
    /*
     * 删除历史全部数据
     */
    public void deleAllHistory() {
        String sql = "DELETE FROM "+ SQLiteDbHelper.BROWSER_HISTORY ;
        db.execSQL(sql);
    }

    //-----------------------------主页导航栏数据库操作---------------------------------

    /**
     * 主页导航栏数据插入
     * @param homeBookBean
     *
     */
    public void insertHomeBook(HomeBookBean homeBookBean){
        String sql = "INSERT INTO " + SQLiteDbHelper.BROWSER_HOMEBOOK + "(homeBookId,homeBookTitle,homeBookUrl,homeBookImage)values(null,?,?,?)";
        Object[] args = {homeBookBean.getHomeBookTitle(),homeBookBean.getHomeBookUrl(),homeBookBean.getHomeBookImage()};
        db.execSQL(sql, args);
//        db.close();
    }

    /**
     * 主页导航栏数据全部数据
     */
    public List<HomeBookBean> queryAllHomeBook(){
        //查询全部数据
        Cursor cursor = db.query(SQLiteDbHelper.BROWSER_HOMEBOOK,null, null, null, null, null, null);
        List<HomeBookBean> list = new ArrayList<>();
        if(cursor.getCount() > 0){
            //移动到首位
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                int homeBookId = cursor.getInt(cursor.getColumnIndex("homeBookId"));
                String homeBookTitle = cursor.getString(cursor.getColumnIndex("homeBookTitle"));
                String homeBookUrl = cursor.getString(cursor.getColumnIndex("homeBookUrl"));
                String homeBookImage = cursor.getString(cursor.getColumnIndex("homeBookImage"));
                HomeBookBean homeBookBean = new HomeBookBean();
                homeBookBean.setHomeBookId(homeBookId);
                homeBookBean.setHomeBookTitle(homeBookTitle);
                homeBookBean.setHomeBookUrl(homeBookUrl);
                homeBookBean.setHomeBookImage(homeBookImage);
                list.add(homeBookBean);
                cursor.moveToNext();
            }
        }
        cursor.close();
        //db.close();
        return list;
    }
    /**
     * 删除单个主页导航栏
     * @param id
     */
    public void deleHomeBook(int id) {
        String sql = "DELETE FROM "+ SQLiteDbHelper.BROWSER_HOMEBOOK +" WHERE "+" historyId ="+"?";
        Object bindArgs[] = new Object[] {id};
        db.execSQL(sql, bindArgs);
    }
    /*
     * 删除主页导航栏全部数据
     */
    public void deleAllHomeBook() {
        String sql = "DELETE FROM "+ SQLiteDbHelper.BROWSER_HOMEBOOK ;
        db.execSQL(sql);
    }

}
